package team.paimengshop.server_database;

import team.paimengshop.server_dataclass.Commodity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class CommodityInfoOperate
{
    private static Connection cn = PaiMengShopDatabase.getConnection();


    public CommodityInfoOperate()
    {

    }


    public static int Add(String name, String introduction, String imageURL, double price)
    {
        String sql = "insert into commodityInfo (name, introduction,  imageURL, price) values ('" + name + "','" + introduction + "','"
                + imageURL + "'," + price + ")";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            n = ps.executeUpdate();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int DeleteByName(String name)
    {
        String sql = "delete from commodityInfo where name = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, name);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int DeleteById(int id)
    {
        String sql = "delete from commodityInfo where id = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, Integer.toString(id));
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static Commodity FindByName(String name)
    {
        Commodity commodity = new Commodity();
        String sql = "select * from commodityinfo where name = ?";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                commodity.setId(rs.getInt(1));
                commodity.setName(rs.getString(2));
                commodity.setIntroduction(rs.getString(3));
                commodity.setImageURL(rs.getString(4));
                commodity.setPrice(rs.getDouble(5));
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return commodity;
    }

    public static Commodity FindById(int id)
    {
        Commodity commodity = new Commodity();
        String sql = "select * from commodityinfo where id = ?";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, Integer.toString(id));
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                commodity.setId(rs.getInt(1));
                commodity.setName(rs.getString(2));
                commodity.setIntroduction(rs.getString(3));
                commodity.setImageURL(rs.getString(4));
                commodity.setPrice(rs.getDouble(5));
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return commodity;
    }

    public static ArrayList<Commodity> GetAllCommodity()
    {
        ArrayList<Commodity> commodityArrayList = new ArrayList<>();
        String sql = "select * from commodityinfo";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Commodity commodity = new Commodity();
                commodity.setId(rs.getInt(1));
                commodity.setName(rs.getString(2));
                commodity.setIntroduction(rs.getString(3));
                commodity.setImageURL(rs.getString(4));
                commodity.setPrice(rs.getDouble(5));
                commodityArrayList.add(commodity);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return commodityArrayList;
    }
}
